{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c47975a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = r'C:/Users/fanyong/Downloads/需求2数据.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "55c8e8b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ae0ea840",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_excel(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d8a7201",
   "metadata": {},
   "outputs": [],
   "source": [
    "1、先给工厂、物料、供应商、有效起始日期、创建日期进行升序排列—该步没有问题，是为了后面方便计算。\n",
    "df2.sort_values(by=[\"工厂\",\"物料\",\"Vendor Name\",\"有效起始日期\",\"创建日期\"],ascending=(True,True,True,True,True))\n",
    "\n",
    "2、为了给excel增加一列：失效开始日和失效结束日，下了下面的代码：\n",
    "df2.loc[:, '失效开始日'] = df2.loc[(df2[\"flag\"]==\"New\"),\"有效起始日期\"]\n",
    "df2.loc[:, '失效结束日'] = df2.loc[(df2[\"flag\"]==\"New\"),\"创建日期\"]\n",
    "但是第一个报错：\n",
    " \n",
    "\n",
    "不过结果里，显示已经给flag为New的记录，失效开始日和失效结束日都赋了值。\n",
    "\n",
    "3、然后，是最重要的，也是一直没有做成功的。\n",
    "因为之前已经对工厂、物料、供应商、有效起始日期、创建日期进行升序排列，\n",
    "Flag为New和Old的数据，其实是同一个零件号的新价格和旧价格。\n",
    "\n",
    "代码：\n",
    "n=1\n",
    "while n in range(len(df2)):\n",
    "    if np.array(df2.loc[[n],['工厂']]) == np.array(df2.loc[[n-1],['工厂']]) and np.array(df2.loc[[n],['物料']]) == np.array(df2.loc[[n-1],['物料']])and np.array(df2.loc[[n],['Vendor Name']]) == np.array(df2.loc[[n-1],['Vendor Name']]) and np.array(df2.loc[[n-1],['flag']] == 'Old'):\n",
    "        df2.loc[[n-1],['失效开始日']] = df2.loc[[n],['失效开始日']]\n",
    "    else:\n",
    "        df2.loc[[n],['失效开始日']] =  df2.loc[[n],['失效开始日']]\n",
    "    \n",
    "n+=1\n",
    "\n",
    "意思是：先让后一个零件跟前一个零件做对比，如果工厂、物料、供应商名称都一样，就看是新价格flag=new还是旧价格 flag=old。\n",
    "如果是新价格那么，因为第2步，已经给flag=new赋值了，如果是同一个零件的旧价格，那么 失效开始日就=新零件的失效开始日。\n",
    "\n",
    "第2个需求：就是图片上的。目的，让失效开始日这一列，如果是第N行跟N-1这一行条件判断之后符合，就第N行的实效开始日=第N-1行的实效开始日"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "63b729b1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>工厂</th>\n",
       "      <th>物料</th>\n",
       "      <th>供应商</th>\n",
       "      <th>Vendor Name</th>\n",
       "      <th>项目</th>\n",
       "      <th>采购凭证类型</th>\n",
       "      <th>有效起始日期</th>\n",
       "      <th>有效期截至日期</th>\n",
       "      <th>付款条件</th>\n",
       "      <th>百分比</th>\n",
       "      <th>Comp.Code Currency</th>\n",
       "      <th>基本计量单位</th>\n",
       "      <th>创建日期</th>\n",
       "      <th>flag</th>\n",
       "      <th>Effective</th>\n",
       "      <th>Sample</th>\n",
       "      <th>失效开始日</th>\n",
       "      <th>失效结束日</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 431</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-05-10</td>\n",
       "      <td>Old</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 431</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-05-10</td>\n",
       "      <td>New</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2021-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>65</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 540</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>Old</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>66</td>\n",
       "      <td>62</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 540</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>2021-09-20 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-04-28</td>\n",
       "      <td>New</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>2021-02-01</td>\n",
       "      <td>2021-04-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>67</td>\n",
       "      <td>68</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 541</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-05-10</td>\n",
       "      <td>Old</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>68</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 541</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>New</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2021-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 542</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>Old</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>75</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 542</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>New</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2021-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>92</td>\n",
       "      <td>93</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 813</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>Old</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>93</td>\n",
       "      <td>94</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 813</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>New</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2021-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>97</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 824</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-05-10</td>\n",
       "      <td>Old</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>98</td>\n",
       "      <td>98</td>\n",
       "      <td>1</td>\n",
       "      <td>A 000 824</td>\n",
       "      <td>11</td>\n",
       "      <td>aa</td>\n",
       "      <td>10</td>\n",
       "      <td>ZMK</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2999-12-31 00:00:00</td>\n",
       "      <td>xx</td>\n",
       "      <td>0</td>\n",
       "      <td>CNY</td>\n",
       "      <td>ST</td>\n",
       "      <td>2021-06-03</td>\n",
       "      <td>New</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>2013-01-09</td>\n",
       "      <td>2021-06-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  Unnamed: 0.1  工厂         物料  供应商 Vendor Name  项目 采购凭证类型  \\\n",
       "0           33            34   1  A 000 431   11          aa  10    ZMK   \n",
       "1           34            35   1  A 000 431   11          aa  10    ZMK   \n",
       "2           65            66   1  A 000 540   11          aa  10    ZMK   \n",
       "3           66            62   1  A 000 540   11          aa  10    ZMK   \n",
       "4           67            68   1  A 000 541   11          aa  10    ZMK   \n",
       "5           68            67   1  A 000 541   11          aa  10    ZMK   \n",
       "6           74            74   1  A 000 542   11          aa  10    ZMK   \n",
       "7           75            75   1  A 000 542   11          aa  10    ZMK   \n",
       "8           92            93   1  A 000 813   11          aa  10    ZMK   \n",
       "9           93            94   1  A 000 813   11          aa  10    ZMK   \n",
       "10          97            97   1  A 000 824   11          aa  10    ZMK   \n",
       "11          98            98   1  A 000 824   11          aa  10    ZMK   \n",
       "\n",
       "       有效起始日期              有效期截至日期 付款条件  百分比 Comp.Code Currency 基本计量单位  \\\n",
       "0  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "1  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "2  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "3  2021-02-01  2021-09-20 00:00:00   xx    0                CNY     ST   \n",
       "4  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "5  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "6  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "7  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "8  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "9  2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "10 2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "11 2013-01-09  2999-12-31 00:00:00   xx    0                CNY     ST   \n",
       "\n",
       "         创建日期 flag Effective Sample      失效开始日      失效结束日  \n",
       "0  2021-05-10  Old         Y      Y        NaT        NaT  \n",
       "1  2021-05-10  New         Y      Y 2013-01-09 2021-05-10  \n",
       "2  2021-06-03  Old         Y      Y        NaT        NaT  \n",
       "3  2021-04-28  New         Y      Y 2021-02-01 2021-04-28  \n",
       "4  2021-05-10  Old         Y      Y        NaT        NaT  \n",
       "5  2021-06-03  New         Y      Y 2013-01-09 2021-06-03  \n",
       "6  2021-06-03  Old         Y      Y        NaT        NaT  \n",
       "7  2021-06-03  New         Y      Y 2013-01-09 2021-06-03  \n",
       "8  2021-06-03  Old         Y      Y        NaT        NaT  \n",
       "9  2021-06-03  New         Y      Y 2013-01-09 2021-06-03  \n",
       "10 2021-05-10  Old         Y      Y        NaT        NaT  \n",
       "11 2021-06-03  New         Y      Y 2013-01-09 2021-06-03  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.sort_values(by=[\"工厂\",\"物料\",\"Vendor Name\",\"有效起始日期\",\"创建日期\"],ascending=(True,True,True,True,True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a34fe3a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    2013-01-09\n",
       "3    2021-02-01\n",
       "5    2013-01-09\n",
       "7    2013-01-09\n",
       "9    2013-01-09\n",
       "11   2013-01-09\n",
       "Name: 有效起始日期, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df2.loc[:, '失效开始日'] = df2.loc[(df2[\"flag\"]==\"New\"),\"有效起始日期\"]\n",
    "# df2.loc[:, '失效结束日'] = df2.loc[(df2[\"flag\"]==\"New\"),\"创建日期\"]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
